package com.apexedu.eas.dao.impl;

import java.util.List;

import org.springframework.stereotype.Repository;
import org.springframework.ui.ModelMap;

import com.apexedu.eas.annotation.Query;
import com.apexedu.eas.entity.Gxjg0101;
import com.apexedu.eas.entity.TXq;
import com.apexedu.eas.entity.TjxCjxx;
import com.apexedu.eas.entity.TjxJhjxb;
import com.apexedu.framework.util.Pager;
import com.apexedu.util.StringUtil;


/**
 * 成绩信息Dao
 * @author admin
 *
 */
@Repository
public class TjxCjxxDao extends BasisDao<TjxCjxx, String> {
	
	//查询成绩
	
	/**
	 * 查询学期名称
	 * @return 学期编号和名称集合
	 */
	public List getXqList() {
		List list =  easDBUtil.queryForList("select id, xqmc from t_xq where zt <> 1 order by xn desc, xqsx desc");
		return list;
	}
	
	/**
	 * 查询开课部门名称
	 * @return  部门编号和名称集合
	 */
	public List getBmList() {
		List list =  easDBUtil.queryForList("SELECT ID, NAME FROM LBORGANIZATION WHERE cdkc=1");
		return list;
	}
	
	/**
	 * 查询院系名称
	 * @return 院系编号和名称集合
	 */
	public List getYxList() {
		List list =  easDBUtil.queryForList("SELECT ID,NAME FROM LBORGANIZATION WHERE  jxxb=1");
		return list;
	}
	
	/**
	 * 查询年级名称
	 * @return 年级编号和名称集合
	 */
	public List getNjList() {
		List list =  easDBUtil.queryForList("select id,njmc from T_NJ order by rxnf desc");
		return list;
	}
	
	/**
	 * 查询课程属性
	 * @return 课程属性编号和名称集合
	 */
	public List getKcsx() {
		List list =  easDBUtil.queryForList("select id,mc FROM T_SJZX_DMJZX where ssdm='HB_KCSX'");
		return list;
	}
	
	/**
	 * 查询未提交成绩人数
	 * @param jxrwid 教学班编号
	 * @return 教学班未提交成绩人数
	 */
	public Integer getCount(String jxrwid){
		String sf = "from tjx_cjxx cj where jxb = "+jxrwid+" and cjzt is null or cjzt = 0      "; 
		return easDBUtil.count(sf);
	}
	
	/**
	 * 查询成绩信息
	 * @param map 传入页面的参数
	 * @param pager 分页参数
	 * @param xh 学号
	 * @param xm 姓名
	 * @param xqString 学期
	 * @param ssyx	所属院系
	 * @param kkbm 开课部门
	 * @param kcmc 课程名称
	 * @param cjzt 成绩状态
	 * @param jxrwid 教学任务（教学班编号）
	 * @return 成绩信息集合
	 */
	public List<TjxCjxx> getListCjxx(ModelMap map,Pager pager,String xh, String xm, String xqString,String ssyx,String kkbm,String kcmc,String cjzt,String jxrwid){
		String select = "SELECT CJ.ID,CJ.XQ,XS.XH,XS.XM,JXB.JXBMC AS BJMC,CJLX.LXBM as CJLX,"
				+ "JXB.PSCJZB,JXB.QZCJZB,JXB.QMCJZB,JXB.QMKGZB,JXB.SZTZZB,JXB.SYCJZB,KC.KCMC,"
				+ "(SELECT NAME FROM T_SJZX_DMJZX SJZX WHERE SJZX.ID = CJ.HB_KSXS  ) AS KHFSMC,"
				+ "(SELECT NAME FROM T_SJZX_DMJZX SJZX WHERE SJZX.ID = CJ.HB_KCSX  ) AS KCSXMC,"
				+ "CJ.ZZFS,CJ.PSCJ,CJ.QZCJ,CJ.QMCJ,CJ.QMKG,CJ.SZTZ,CJ.SYCJ,cj.zpcj,nvl(cj.cjzt,0) cjzt\n";
		//返回 待输入学生成绩信息
		StringBuilder sb = new StringBuilder("from tjx_cjxx cj "
				+ "left join tjx_jhjxb jxb on cj.jxb=jxb.id "
				+ "left join TJX_CJLX cjlx on jxb.cjlx=cjlx.id "
				+ "LEFT JOIN GXXS0101 XS ON CJ.GXXS0101=XS.ID "
				+ "LEFT JOIN TJX_KC_GXJX02 KC ON CJ.KC=KC.ID "
				+ "where JXB.ID = " + jxrwid + " \n");
		
		if (StringUtil.isNotEmpty(xh)) {//学号
			map.put("xh", xh);
			sb.append(" and xs.xh = '" + xh + "'");
		}
		if (StringUtil.isNotEmpty(xm)) {//姓名
			map.put("xm", xm);
			sb.append(" and xs.xm = '" + xm + "'");
		}
		if (StringUtil.isNotEmpty(xqString)) {//学期
			map.put("xqid", xqString);
			sb.append(" and cj.xq = '" + xqString + "'");
		}
		if (StringUtil.isNotEmpty(ssyx)) {//所属院系
			map.put("ssyx", ssyx);
			sb.append(" and xs.zzjg = " + ssyx);
		}
		if (StringUtil.isNotEmpty(kkbm)) {//开课部门
			map.put("kkbm", kkbm);
			sb.append(" and kc.zzjg = " + kkbm);
		}
		if (StringUtil.isNotEmpty(kcmc)) {//课程名称
			map.put("kcmc", kcmc);
			sb.append(" and kc.kcmc like '%" + kcmc + "%'");
		}
		//...
		if (StringUtil.isNotEmpty(cjzt)) {//成绩状态
			if (Integer.parseInt(cjzt) == 1) {
				sb.append(" and cj.cjzt = '1' ");
			} else {
				sb.append(" and cj.cjzt = '0' ");
			}
			map.put("cjzt", cjzt);
		}
		
		sb.append(" order by xs.xh");
		String sql = sb.toString();
		long num = easDBUtil.count(sql);
		//设置分页
		pager.setTotalRows(num);
		//设置每页个数
		pager.setEachPageRows(40);
		return easDBUtil.queryPageBeanList(pager, select+sql, TjxCjxx.class);
	}
	
	
	/**
	 * 查询成绩信息
	 * @param map 传入页面的参数
	 * @param pager 分页参数
	 * @param xh 学号
	 * @param xm 姓名
	 * @param xqString 学期
	 * @param ssyx	所属院系
	 * @param kkbm 开课部门
	 * @param kcmc 课程名称
	 * @param cjzt 成绩状态
	 * @param jxrwid 教学任务（教学班编号）
	 * @return 成绩信息集合
	 */
	public List<TjxCjxx> getListCjxx2(ModelMap map,Pager pager,String xh, String xm, String xqString,String ssyx,String kkbm,String kcmc,String cjzt,String jxrwid){
		String select = "SELECT CJ.ID,CJ.XQ,XS.XH,XS.XM,JXB.JXBMC AS BJMC,"
				+ "CJLX.LXBM as CJLX,JXB.PSCJZB,JXB.QZCJZB,JXB.QMCJZB,KC.KCMC,"
				+ "(SELECT NAME FROM T_SJZX_DMJZX SJZX WHERE SJZX.ID = CJ.HB_KSXS  ) AS KHFSMC,"
				+ "(SELECT NAME FROM T_SJZX_DMJZX SJZX WHERE SJZX.ID = CJ.HB_KCSX  ) AS KCSXMC,"
				+ "CJ.ZZFS,CJ.PSCJ,CJ.QZCJ,CJ.QMCJ, cj.zpcj,nvl(cj.cjzt,0) cjzt\n";
		//返回 待输入学生成绩信息
		StringBuilder sb = new StringBuilder("from tjx_cjxx cj "
				+ "left join tjx_jhjxb jxb on cj.jxb=jxb.id "
				+ "left join TJX_CJLX cjlx on jxb.cjlx=cjlx.id "
				+ "LEFT JOIN GXXS0101 XS ON CJ.GXXS0101=XS.ID "
				+ "LEFT JOIN TJX_KC_GXJX02 KC ON CJ.KC=KC.ID "
				+ "where JXB.ID = " + jxrwid + " \n");
		
		if (StringUtil.isNotEmpty(xh)) {//学号
			map.put("xh", xh);
			sb.append(" and xs.xh = '" + xh + "'");
		}
		if (StringUtil.isNotEmpty(xm)) {//姓名
			map.put("xm", xm);
			sb.append(" and xs.xm = '" + xm + "'");
		}
		if (StringUtil.isNotEmpty(xqString)) {//学期
			map.put("xqid", xqString);
			sb.append(" and cj.xq = '" + xqString + "'");
		}
		if (StringUtil.isNotEmpty(ssyx)) {//所属院系
			map.put("ssyx", ssyx);
			sb.append(" and xs.zzjg = " + ssyx);
		}
		if (StringUtil.isNotEmpty(kkbm)) {//开课部门
			map.put("kkbm", kkbm);
			sb.append(" and kc.zzjg = " + kkbm);
		}
		if (StringUtil.isNotEmpty(kcmc)) {//课程名称
			map.put("kcmc", kcmc);
			sb.append(" and kc.kcmc like '%" + kcmc + "%'");
		}
		//...
		if (StringUtil.isNotEmpty(cjzt)) {//成绩状态
			if (Integer.parseInt(cjzt) == 1) {
				sb.append(" and cj.cjzt = '1' ");
			} else {
				sb.append(" and cj.cjzt = '0' ");
			}
			map.put("cjzt", cjzt);
		}
		
		sb.append(" order by xs.xh");
		String sql = sb.toString();
		long num = easDBUtil.count(sql);
		//设置分页
		pager.setTotalRows(num);
		//设置每页个数
		pager.setEachPageRows(40);
		return easDBUtil.queryPageBeanList(pager, select+sql, TjxCjxx.class);
	}
	
	/**
	 * 根据id查询教师信息
	 * @param userid 用户
	 * @return 教工对象
	 */
	@Query(value = "select id,gh,xm from GXJG0101 where gh = ?" )
	public Gxjg0101 getGxjg0101ByUserid(String userid) {
		return null;
	}
	
	/**
	 * 根据id查询教师教学任务
	 * @param userid 用户
	 * @return 
	 */
	public long getcountByUserid(Integer userid) {
		long num = easDBUtil.count(" from TJX_JHJXB_JSBJS where sflcj = 1 and GXJG0101 = ?",userid);
		return num;
	}
	/**
	 * 是否是成绩管理员
	 * @param userid 用户
	 * @return
	 */
	public long getTuserByUserid(Integer userid) {
		long num = easDBUtil.count(" FROM TUSER WHERE GXJG0101 = ? and ID = (SELECT USERID FROM LBMEMBER WHERE ROLEID=(SELECT ID FROM LBROLE WHERE NAME='教务处成绩管理员'))",userid);
		return num;
	}
	
	
	/**
	 * 根据学期查询教学班信息(admin)
	 * @param long1 学期id
	 * @return 教学班信息信息
	 */
	@Query(value = "SELECT jxb.id,jxb.jxbmc,jxb.JXBRS as xn,"
					+ "(select count(1) from tjx_cjxx cj where cj.jxb =js.jxb and zzfs is null) as xq "
					+ "FROM TJX_JHJXB JXB "
					+ "LEFT JOIN TJX_JHJXB_JSBJS JS ON JXB.ID = JS.JXB "
					+ "where js.jxb is not null and jxb.xq = ? and (jxb.cjzt is null or jxb.cjzt = 0) "
					+ "AND JXB.ID IN (select JXB from TJX_CJXX GROUP BY JXB )"
					+ "order by jxb.jxbmc,jxb.id" )
	public List<TjxJhjxb> getListByXqid(Long long1) {
		return null;
	}
	
	/**
	 * 根据学期教师工号查询教学班信息
	 * @param xqid 学期id
	 * @return 教学班信息信息
	 */
	@Query(value = "SELECT jxb.id,jxb.jxbmc,jxb.JXBRS as xn,"
			+ "(select count(1) from tjx_cjxx cj where cj.jxb =js.jxb and zzfs is null) as xq "
			+ "FROM TJX_JHJXB JXB "
			+ "LEFT JOIN TJX_JHJXB_JSBJS JS ON JXB.ID = JS.JXB where js.jxb is not null "
			+ "and js.GXJG0101 = ? and jxb.xq = ? and (jxb.cjzt is null or jxb.cjzt = 0) "
			+ "AND JXB.ID IN (select JXB from TJX_CJXX GROUP BY JXB )"
			+ "order by jxb.jxbmc,jxb.id" )
	public List<TjxJhjxb> getListByXqidAndUserid(Integer xqid,Long long1) {
		return null;
	}
	
	/**
	 * 查询成绩录入时间
	 * @return
	 */
	@Query(value = "SELECT ID from tjx_cjlrsjsz where czdm = 'ZPCJLR' and zt = 1 and xq is not null AND SYSDATE BETWEEN KSSJ AND JSSJ" )
	public Object getCjlrsj() {
		return null;
	}
	
	/**
	 * 查询学期信息
	 * @param xqid 学期id
	 * @return 学期对象
	 */
	@Query(value = "select xq.* from T_XQ xq, tjx_cjlrsjsz sz where sz.xq = xq.id and sz.id = ?")
	public TXq getTXqByXqid(String xqid) {
		return null;
	}
	
//	/**
//	 * 查询学期信息
//	 * @param xqid 学期id
//	 * @return 学期对象
//	 */
//	public TXq getTXqByXqid(String xqid) {
//		TXq xq = easDBUtil.queryBean("select xq.* from T_XQ xq, tjx_cjlrsjsz sz where sz.xq = xq.id and sz.id = ?", TXq.class, xqid);
//		return xq;
//	}
	
	//更新成绩
	
	/**
	 * 查询成绩类型
	 * @param jxrwid 教学班编号
	 * @return
	 */
	public List getDjList(String jxrwid) {
		List list = easDBUtil.queryForList("SELECT * FROM TJX_CJLXDJ WHERE cjlx = (SELECT CJLX FROM TJX_JHJXB WHERE ID= "+jxrwid+") order by jd desc");
		if(list.size() == 0){
			return null;
		}else{
			return list;
		}
	}
	
	/**
	 * 根据等级名称查询绩点
	 * @param djmc 等级名称
	 * @return 等级绩点
	 */
	@Query(value = "select jd from TJX_CJLXDJ where djmc = ?" )
	public Object getJdByDjmc(Object djmc) {
		return null;
	}
	
	/**
	 * 查询平时成绩
	 * @param cjxxid 成绩编号
	 * @return 平时成绩
	 */
	public Object queryPscjValue(Object cjxxid){
		return easDBUtil.queryFieldValue("select pscj from tjx_cjxx where id = ?", cjxxid);
	}
	/**
	 * 查询期中成绩
	 * @param cjxxid 成绩编号
	 * @return 期中成绩
	 */
	public Object queryQzcjValue(Object cjxxid){
		return easDBUtil.queryFieldValue("select qzcj from tjx_cjxx where id = ?", cjxxid);
	}
	
	/**
	 * 查询期末成绩
	 * @param cjxxid 成绩编号
	 * @return 期末成绩
	 */
	public Object queryQmcjValue(Object cjxxid){
		return easDBUtil.queryFieldValue("select qmcj from tjx_cjxx where id = ?", cjxxid);
	}
	/**
	 * 查询期末客观成绩
	 * @param cjxxid 成绩编号
	 * @return 期末客观成绩
	 */
	public Object queryQmkgValue(Object cjxxid){
		return easDBUtil.queryFieldValue("select qmkg from tjx_cjxx where id = ?", cjxxid);
	}
	/**
	 * 查询实验成绩
	 * @param cjxxid 成绩编号
	 * @return 实验成绩
	 */
	public Object querySycjValue(Object cjxxid){
		return easDBUtil.queryFieldValue("select sycj from tjx_cjxx where id = ?", cjxxid);
	}
	/**
	 * 查询素质拓展成绩
	 * @param cjxxid 成绩编号
	 * @return 素质拓展成绩
	 */
	public Object querySztzValue(Object cjxxid){
		return easDBUtil.queryFieldValue("select sztz from tjx_cjxx where id = ?", cjxxid);
	}
	
	
	/**
	 * 
	 * @param sql
	 * @return
	 */
//	public Object queryFieldValue(String sql){
//		return easDBUtil.queryFieldValue(sql);
//	}
	
	/**
	 * 查询绩点计算语法
	 * @param cjlx 成绩类型
	 * @return 绩点计算语法
	 */
	public Object queryJdjsyf(Object cjlx){
		return easDBUtil.queryFieldValue("select jdjsyf from TJX_CJLX where lxbm = ?",cjlx);
	}
	
	/**
	 * 查询不同成绩类型的及格线
	 * @param cjlx 成绩类型
	 * @return 及格线
	 */
	public Object queryJgx(Object cjlx){
		return easDBUtil.queryFieldValue("select jgx from TJX_CJLX where lxbm = ?",cjlx);
	}
	
	/**
	 * 更新平时成绩
	 * @param pscj 平时成绩
	 * @param jd 绩点
	 * @param zzfs 最终分数
	 * @param hdxf 获得学分
	 * @param cjxxid 成绩编号
	 */
	public void updatePscj(Object pscj,Double jd,Double zzfs, Integer hdxf, String cjxxid){
		String sql = "update tjx_cjxx set pscj = ?,zpcj = ?, zzfs = ?, zzcj = ?, jd = "+jd+",xf = " + hdxf
		+ "*yhxf where id = ?";
		easDBUtil.executeSQL(sql, pscj, zzfs, zzfs, zzfs, cjxxid);
	}
	/**
	 * 更新期中成绩
	 * @param qzcj 期中成绩
	 * @param jd 绩点
	 * @param zzfs 最终分数
	 * @param hdxf 获得学分
	 * @param cjxxid 成绩编号
	 */
	public void updateQzcj(Object qzcj,Double jd,Double zzfs, Integer hdxf, String cjxxid){
		String sql = "update tjx_cjxx set qzcj = ?,zpcj = ?,zzfs = ?, zzcj = ?, jd = "+jd+",xf = " + hdxf
		+ "*yhxf where id = ?";
		easDBUtil.executeSQL(sql, qzcj, zzfs, zzfs, zzfs, cjxxid);
	}
	
	/**
	 * 更新期末成绩
	 * @param qmcj 期末成绩
	 * @param jd 绩点
	 * @param zzfs 最终分数
	 * @param hdxf 获得学分
	 * @param cjxxid 成绩编号
	 */
	public void updateQmcj(Object qmcj,Double jd,Double zzfs, Integer hdxf, String cjxxid){
		String sql = "update tjx_cjxx set qmcj = ?, zpcj = ?, zzfs = ?, zzcj = ?, jd = "+jd+",xf = " + hdxf
		+ "*yhxf where id = ?";
		easDBUtil.executeSQL(sql, qmcj, zzfs, zzfs, zzfs, cjxxid);
	}
	
	/**
	 * 更新期末客观成绩
	 * @param qmkg 期末客观成绩
	 * @param jd 绩点
	 * @param zzfs 最终分数
	 * @param hdxf 获得学分
	 * @param cjxxid 成绩编号
	 */
	public void updateQmkg(Object qmkg,Double jd,Double zzfs, Integer hdxf, String cjxxid){
		String sql = "update tjx_cjxx set qmkg = ?, zpcj = ?, zzfs = ?, zzcj = ?, jd = "+jd+",xf = " + hdxf
		+ "*yhxf where id = ?";
		easDBUtil.executeSQL(sql, qmkg, zzfs, zzfs, zzfs, cjxxid);
	}
	/**
	 * 更新实验成绩
	 * @param sycj 实验成绩
	 * @param jd 绩点
	 * @param zzfs 最终分数
	 * @param hdxf 获得学分
	 * @param cjxxid 成绩编号
	 */
	public void updateSycj(Object sycj,Double jd,Double zzfs, Integer hdxf, String cjxxid){
		String sql = "update tjx_cjxx set sycj = ?, zpcj = ?, zzfs = ?, zzcj = ?, jd = "+jd+",xf = " + hdxf
		+ "*yhxf where id = ?";
		easDBUtil.executeSQL(sql, sycj, zzfs, zzfs, zzfs, cjxxid);
	}
	/**
	 * 更新素质拓展成绩
	 * @param sztz 素质拓展成绩 
	 * @param jd 绩点
	 * @param zzfs 最终分数
	 * @param hdxf 获得学分
	 * @param cjxxid 成绩编号
	 */
	public void updateSztz(Object sztz,Double jd,Double zzfs, Integer hdxf, String cjxxid){
		String sql = "update tjx_cjxx set sztz = ?, zpcj = ?, zzfs = ?, zzcj = ?, jd = "+jd+",xf = " + hdxf
		+ "*yhxf where id = ?";
		easDBUtil.executeSQL(sql, sztz, zzfs, zzfs, zzfs, cjxxid);
	}
	
	/**
	 * 更新平时成绩为null
	 * @param cjxxid 成绩编号
	 */
	public void updatePscj2(String cjxxid){
		easDBUtil.executeSQL("update tjx_cjxx set pscj = null,zpcj = null, zzfs = null,zzcj= null,jd = null,xf = null where id = ?", cjxxid);
	}
	/**
	 * 更新期中成绩为null
	 * @param cjxxid 成绩编号
	 */
	public void updateQzcj2(String cjxxid){
		easDBUtil.executeSQL("update tjx_cjxx set qzcj = null,zpcj = null,zzfs = null,zzcj= null,jd = null,xf = null where id = ?", cjxxid);
	}
	/**
	 * 更新期末成绩为null
	 * @param cjxxid 成绩编号
	 */
	public void updateQmcj2(String cjxxid){
		easDBUtil.executeSQL("update tjx_cjxx set qmcj = null,zpcj = null,zzfs = null,zzcj= null,jd = null,xf = null where id = ?", cjxxid);
	}
	
	/**
	 * 更新期末客观成绩为null
	 * @param cjxxid 成绩编号
	 */
	public void updateQmkg2(String cjxxid){
		easDBUtil.executeSQL("update tjx_cjxx set qmkg = null,zpcj = null,zzfs = null,zzcj= null,jd = null,xf = null where id = ?", cjxxid);
	}
	/**
	 * 更新实验成绩为null
	 * @param cjxxid 成绩编号
	 */
	public void updateSycj2(String cjxxid){
		easDBUtil.executeSQL("update tjx_cjxx set sycj = null,zpcj = null,zzfs = null,zzcj= null,jd = null,xf = null where id = ?", cjxxid);
	}
	/**
	 * 更新素质拓展成绩为null
	 * @param cjxxid 成绩编号
	 */
	public void updateSztz2(String cjxxid){
		easDBUtil.executeSQL("update tjx_cjxx set sztz = null,zpcj = null,zzfs = null,zzcj= null,jd = null,xf = null where id = ?", cjxxid);
	}
	
	//成绩提交
	
	/**
	 * 成绩录入状态
	 * @param xqid	学期id
	 * @param jgid 教工工号id
	 * @param jxrwid 教学班id
	 * @return 未录入人数
	 */
	public Integer getCjzt(String xqid, String jgid, String jxrwid){
		String sql = ("select count(1) from tjx_cjxx where xq = ? and jxb = ? and zzfs is null");
		return Integer.parseInt(easDBUtil.queryFieldValue(sql, xqid, jxrwid).toString());
	}
	
	/**
	 * 更新学生与教学班成绩状态状态
	 * @param xqid	学期id
	 * @param jgid 教工工号id
	 * @param jxrwid 教学班id
	 */
	public void updateCjzt(String xqid, String jgid, String jxrwid){
//		easDBUtil.executeSQL("update tjx_cjxx set cjzt = 1 where xq = ? and dfjs = ? and jxb = ?", xqid, jgid, jxrwid);
		easDBUtil.executeSQL("update tjx_cjxx set cjzt = 1 where xq = ? and jxb = ?", xqid, jxrwid);
		easDBUtil.executeSQL("UPDATE tjx_jhjxb SET cjzt = 1 WHERE id = ?",jxrwid);
		
	}
	
	
	
}
